package cn.vastbase.jdbc;

import org.junit.Test;

import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

public class DateTypeTestCase {
    /* 定义连接对象 */
    static Connection conn = null;
    static String cname = "org.postgresql.Driver";
    static String url = "jdbc:postgresql://192.168.56.12:6000/test?loggerLevel=OFF";
    static String username = "zxs";
    static String passwd = "Vastdata#0730";
    /* 定义 SQL 语句执行对象 */
    static Statement stmt = null;
    static PreparedStatement pstmt = null;
    /* 定义结果集对象 */
    static ResultSet rs = null;


    @Test
    public void selectByDate() {
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("select title from t where log_date between ? and ?");
            pstmt.setDate(1, Date.valueOf(LocalDate.of(2024, 8, 25)));
            pstmt.setDate(2, Date.valueOf(LocalDate.of(2024, 8, 27)));
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println("prague_sys_log[title=" + rs.getString(1) + "]");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }


    /**
     * CREATE TABLE t_datatype_test(a datetime);
     * insert into t_datatype_test values (now());
     */
    @Test
    public void selectDate() {
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("select * from t_datatype_test");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Object object = rs.getObject(1, LocalDateTime.class);
                System.out.println(object.getClass().getSimpleName());
                System.out.println(object);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }

    @Test
    public void selectTinyint() {
        try {
            if (conn() == null)
                return;
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select a from t_tint1");
            while (rs.next()) {
                Object obj = rs.getObject(1);
                System.out.println("t_tint[a=" + obj.getClass().getSimpleName() + "]");
                System.out.println("t_tint[a=" + obj + "]");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }

    /**
     * create table tt(a int, b number(5,2));
     * insert into tt values(1,1.1),(2,2.2);
     */
    @Test
    public void sum() {
        try {
            url = "jdbc:postgresql://192.168.56.12:6000/vastbase?db_compatibility=mysql&loggerLevel=off";
            username = "test";
            passwd = "Vastdata#0730";
            if (conn() == null)
                return;
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select sum(1) ");
            while (rs.next()) {
                System.out.println("tt[a=" + rs.getObject(1).getClass().getSimpleName() + "]");
//                System.out.println("tt[b=" + rs.getObject(2).getClass().getSimpleName() + "]");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }

    /**
     * 空串处理,mysql getInt -0, g100 报错
     */
    @Test
    public void emptyStr() {
        try {
//            cname = "com.mysql.jdbc.Driver";
//            username = "root";
//            url = "jdbc:mysql://172.16.55.150:3306/zxs";
            if (conn() == null)
                return;

            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from empty_table");
            while (rs.next()) {
                int o = rs.getInt(1);
            }
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
        } finally {
            close();
        }
    }

    /*

    CREATE OR REPLACE VIEW v_num as
    select count(a) AS a1,
    nvl(count(a),0) AS a2,
    case when (SELECT sum(a) FROM dual) IS null then 0 else 1 end as a3
    from test_num GROUP BY a;
    Oracle 兼容模式下，设置db_compatibility=oracle，返回的都是Bigdecimal类型和Oracle一样.
    如果不加db_compatibility=oracle，Long,Long,Integer
     */
    @Test
    public void selectNumber() {
//        cname = "oracle.jdbc.driver.OracleDriver";
//        url = "jdbc:oracle:thin:@172.16.55.186:1521/ORCLPDB1";
//        username = "ZXS";
//        passwd = "Vastdata#0730";

        url = "jdbc:postgresql://192.168.56.12:5000/test?loggerLevel=OFF";
        username = "test";
        passwd = "Vastdata#0730";
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("select * from t_t2");
//            pstmt = conn.prepareStatement("select count(a) AS a1, \n" +
//                    "nvl(count(a),0) AS a2, \n" +
//                    "case when (SELECT sum(a) FROM dual) IS null then 0 else 1 end as a3 \n" +
//                    "from test_num GROUP BY a;");
            rs = pstmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (rs.next()) {
                for (int i = 0; i < columnCount; i++) {
                    Object value = rs.getObject(i + 1);
                    System.out.println("Java类型: " + value.getClass().getSimpleName()
                            + ", 字段类型" + metaData.getColumnTypeName(1)
                            + ", 字段名：" + metaData.getColumnName(1)
                            + ", 字段长度：" + metaData.getColumnDisplaySize(1)
                            + ", 字段精度：" + metaData.getPrecision(1)
                            + ", 字段小数位：" + metaData.getScale(1)
                            + ", 值：" + value);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }

    @Test
    public void testLong() {
        cname = "cn.com.vastbase.Driver";
        url = "jdbc:vastbase://192.168.56.11:5432/vastbase?currentSchema=test&db_compatibility=oracle&stringtype=unspecified&loggerLevel=OFF";
        username = "test";
        passwd = "Vastdata#0730";
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("update t1 set a=? where b=?");
            pstmt.setLong(1, 1234567890123456789L);
            pstmt.setLong(2, 1L);
            pstmt.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            close();
        }
    }


    @Test
    public void testUint() {
        cname = "cn.com.vastbase.Driver";
        url = "jdbc:vastbase://192.168.56.11:6000/test";
        try {
            if (conn() == null)
                return;
            stmt = conn.createStatement();
            stmt.execute("create table if not exists tyint_test( a tinyint UNSIGNED, b varchar(10) ); " +
                    "insert into tyint_test values (1,'111');");
            rs = stmt.executeQuery("select a from tyint_test limit 1");
            rs.next();
            Object o1 = rs.getObject(5);
            System.out.println(o1.getClass().getName() + "," + o1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }


    @Test
    public void testFloat() {
        url = "jdbc:postgresql://192.168.56.12:6000/vastbase?db_compatibility=mysql&loggerLevel=off&extraFloatDigits=3";
        username = "test";
        passwd = "Vastdata#0730";
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("insert into t5 (a)  select ?;");
            System.out.println(29.516f);
            pstmt.setFloat(1, 29.516f);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }

    /**
     * 输出
     * Java类型: BigDecimal, 字段类型serial, 字段名：ID, 字段长度：11, 字段精度：10, 字段小数位：0, 值：1
     * Java类型: Float, 字段类型float4, 字段名：VALUE, 字段长度：15, 字段精度：8, 字段小数位：8, 值：NaN
     * NaN是近似值，所以Float合理
     */
    @Test
    public void testNumeric() {
        cname = "cn.com.vastbase.Driver";
        url = "jdbc:postgresql://172.16.55.175:5432/vastbase?db_compatibility=oracle&loggerLevel=trace";
        username = "zxs";
        passwd = "Vastdata#0730";

//        cname = "oracle.jdbc.driver.OracleDriver";
//        url = "jdbc:oracle:thin:@172.16.55.146:1521/C190";
//        username = "C##ZXS";
//        passwd = "Vastdata#0730";
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("select * from example where id=?");
            pstmt.setInt(1, 1);
            rs = pstmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (rs.next()) {
                for (int i = 0; i < columnCount; i++) {
                    Object value = rs.getObject(i + 1);
                    System.out.println("Java类型: " + value.getClass().getSimpleName()
                            + ", 字段类型" + metaData.getColumnTypeName(i + 1)
                            + ", 字段名：" + metaData.getColumnName(i + 1)
                            + ", 字段长度：" + metaData.getColumnDisplaySize(i + 1)
                            + ", 字段精度：" + metaData.getPrecision(i + 1)
                            + ", 字段小数位：" + metaData.getScale(i + 1)
                            + ", 值：" + value);
                    if (i == 1) {
                        System.out.println(rs.getFloat(i + 1));
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }

    @Test
    public void testSmallInt() {
        cname = "org.postgresql.Driver";
        url = "jdbc:postgresql://192.168.56.17:6000/vastbase?db_compatibility=mysql&loggerLevel=off";
        username = "test";
        passwd = "Vastdata#0730";
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("create table if not exists t_smallint(a smallint); insert into t_smallint values (?);");
            pstmt.setBoolean(1, false);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }


    @Test
    public void testBit() {
        cname = "org.postgresql.Driver";
        url = "jdbc:postgresql://192.168.56.17:6000/vastbase?loggerLevel=off";
        username = "test";
        passwd = "Vastdata#0730";
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("drop table if exists t_bit; create table if not exists t_bit(a bit(1)); insert into t_bit values (?);");
            byte c = 10;
            pstmt.setByte(1, c);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }


    @Test
    public void testDdl() {
        cname = "cn.com.vastbase.Driver";
        url = "jdbc:vastbase://192.168.56.12:6000/vastbase?loggerLevel=off";
        username = "test";
        passwd = "Vastdata#0730";
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("create table t33(a int) comment = ? ");
            pstmt.setString(1, "测试表");
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }


    @Test
    public void testTimestamp() {
        cname = "cn.com.vastbase.Driver";
        url = "jdbc:vastbase://192.168.56.17:5432/testdb_mysql?loggerLevel=off";
        username = "test";
        passwd = "Vastdata#0730";
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("select (now() - interval ?) as dif;");
            pstmt.setString(1, "30 sec");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getObject(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }

    public void testInterval() {
        cname = "cn.com.vastbase.Driver";
        url = "jdbc:vastbase://192.168.56.17:5432/testdb_mysql?loggerLevel=off";
        username = "test";
        passwd = "Vastdata#0730";
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("select (now()::timestamp - ?) as dif;");
            pstmt.setString(1, "30 sec");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getObject(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }


    @Test
    public void testTimestamp24() {
        cname = "org.postgresql.Driver";
        url = "jdbc:postgresql://192.168.56.12:5432/test_mysql?loggerLevel=off";
        username = "test";
        passwd = "Vastdata#0730";
        try {
            if (conn() == null)
                return;

            pstmt = conn.prepareStatement("select count(*) from t_pending where create_time > ? and create_time <= ?;");
            pstmt.setTimestamp(1, Timestamp.valueOf(LocalDateTime.parse("2020-01-01 00:00:00", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))));
            pstmt.setTimestamp(2, Timestamp.valueOf(LocalDateTime.parse("2026-01-01 00:00:00", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))));
//            pstmt.setDate(1, Date.valueOf(LocalDate.parse("2020-01-01 00:00:00", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))));
//            pstmt.setDate(2, Date.valueOf(LocalDate.parse("2026-01-01 00:00:00", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))));
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getObject(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }


    @Test
    public void testImage() {
        cname = "cn.com.vastbase.Driver";
        url = "jdbc:vastbase://192.168.56.17:8000/vastbase?loggerLevel=off";
        username = "test";
        passwd = "Vastdata#0730";
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("select notice_content from office_notice where id = ?");
            pstmt.setLong(1, 51859L);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getMetaData().getColumnTypeName(1));
                Object object = rs.getObject(1);
                System.out.println(object.getClass().getName());
                byte[] bytes = rs.getBytes(1);
                System.out.println(new String(bytes));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }


    public Connection conn() {
        try {
            Class.forName(cname);
            return conn = DriverManager.getConnection(url, username, passwd);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return null;

    }

    public void close() {
        try {
            /* 关闭结果集对象 */
            if (rs != null)
                rs.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close resultSet fail." + e.getMessage());
        }
        try {
            /* 关闭语句对象 */
            if (stmt != null)
                stmt.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close statement fail." + e.getMessage());
        }
        try {
            /* 关闭语句对象 */
            if (pstmt != null)
                pstmt.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close statement fail." + e.getMessage());
        }
        try {
            /* 关闭连接 */
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close connection fail." + e.getMessage());
        }
    }


}
